﻿/* ----------------------------------------------------------------------------
                 value:type checking in exp_values/exp_variables
---------------------------------------------------------------------------- */

-- drop function validate_exp_values();
CREATE OR REPLACE FUNCTION public.validate_exp_values() RETURNS trigger AS
/*
Check whether the values in exp_values.s_value can be cast to the type given in
exp_variables.variable_type. Note that the values in exp_design.s_value are not directly
checked because a foreign key links exp_values to exp_design.
*/
$BODY$
DECLARE
    sqlstring text;
    value_type_array text[];

BEGIN
sqlstring := 'SELECT DISTINCT ARRAY[exp_values.s_value, exp_variables.variable_type]
              FROM exp_values INNER JOIN exp_variables ON exp_values.s_variable = exp_variables.s_variable';

FOR value_type_array IN EXECUTE sqlstring
LOOP
    sqlstring := 'select ' || quote_literal(value_type_array[1]) || '::' || value_type_array[2];
    EXECUTE sqlstring;
END LOOP;
RETURN null;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.validate_exp_values()
  OWNER TO dberaldi;

drop trigger if exists trigger_validate_exp_values on exp_values;
create constraint trigger trigger_validate_exp_values after insert or update
/* If a value in exp_values is changed, check whether
the new value is allowed by the variable type in exp_variables.variable_type */
    on exp_values deferrable initially deferred for each row
    execute procedure public.validate_exp_values();

drop trigger if exists trigger_validate_exp_values on exp_variables;
create constraint trigger trigger_validate_exp_values after insert or update
/* Check if changes in exp_variables (variable_type) are compatible with the 
correpsonding values in exp_values.s_value */
    on exp_variables deferrable initially deferred for each row
    execute procedure public.validate_exp_values(); 

/* ----------------------------------------------------------------------------
                  Functions to cross tabulate projects
---------------------------------------------------------------------------- */

create or replace function main.update_crosstab_exp_design() returns trigger AS 
    /*
    Procedure triggered by trigger_crosstab_exp_design.
    When table exp_design is modified the cross-tab version of it will be re-created.

    TO DO:
    - Check destintaion table is not already in public schema otherwise alter will fail.
    */
    $body$
    begin
    set search_path to materialized_views, main, public, django_admin; -- This will create the matview in the proper schema

    drop table if exists materialized_views.exp_design_samples cascade;
    perform cross_tab(' 
        select distinct
            exp_design.sample_id,
            exp_design.s_variable, -- For cross_tab: This must be second last column
            exp_design.s_value     -- For cross_tab: This must be last column
        from exp_design 
        left join project_samples on exp_design.sample_id = project_samples.sample_id
        inner join samples on exp_design.sample_id = samples.sample_id
        left join libraries on samples.sample_id = libraries.sample_id
        ', 'exp_design_samples', temp := False, overwrite := True, cascade := True);

    alter table exp_design_samples add column id serial;
    
    drop view if exists view_exp_design_samples;
    create view materialized_views.view_exp_design_samples AS(
                 select distinct 
                     exp_design_samples.*,
                     samples.organism,
                     samples.source_name,
                     samples.molecule,
                     libraries.library_id,
                     libraries.library_type
                 from exp_design_samples inner join samples on exp_design_samples.sample_id = samples.sample_id
                 left join libraries on libraries.sample_id = samples.sample_id);
    comment on view materialized_views.view_exp_design_samples is 'Table created by procedure main.update_crosstab_exp_design() triggered by "trigger_crosstab_exp_design" on update/insert/delete on table exp_design. Do not modify this table directly.';
    return null;
    end;
    $body$
language plpgsql;

drop trigger if exists trigger_update_crosstab_exp_design on exp_design;
create trigger trigger_update_crosstab_exp_design after insert or update or delete 
    on exp_design
    execute procedure main.update_crosstab_exp_design();

/* ----------------------------------------------------------------------------
                 Cross-tabaluated experimental designs by project
---------------------------------------------------------------------------- */

-- drop function create_crosstab_project_designs();
create or replace function main.create_crosstab_project_designs() returns trigger AS 
    /*
    Create a cross-tab table for *each* design project (possibly where is_deprectaed is False)
    */
    $body$
    declare 
        sqlstring text;
        sqlcounter text;
        nrows int;
        row_project projects%ROWTYPE; -- Name of the project
        ct_project text; -- Name of cross tab table produced from exp_design
        view_project text; -- Name for final view
    begin
    set search_path to materialized_views, main, public, django_admin; -- This will create the matview in the proper schema
    for row_project in select distinct project from projects where is_deprecated is False
        loop
        /* MEMO: Each table joined in triggered function must have a trigger associated 
         otherwise the cross-tabs will be outdated !!*/
            ct_project := 'exp_design_' || row_project.project;
            view_project := 'view_' || row_project.project;
            sqlstring := 'select distinct 
                                 project_samples.project,
                                 exp_design.sample_id,
                                 exp_design.s_variable, -- For cross_tab: This must be second last column 
                                 exp_design.s_value     -- For cross_tab: This must be last column
                          from exp_design inner join project_samples on exp_design.sample_id = project_samples.sample_id 
                          where project_samples.project = ' || quote_literal(row_project.project);
            sqlcounter := 'select count(*) from (' || sqlstring || ') as t';
            execute sqlcounter into nrows;
            if nrows > 0 then
                perform cross_tab(sqlstring, ct_project, False, True, cascade := True);
                execute 'alter table ' || quote_ident(ct_project) || ' add column id serial';
                execute 'comment on table ' || quote_ident(ct_project) || $$ is 'Table created by create_crosstab_project_designs() triggered by changes on exp_design (and possibly ther tables)'$$;
                execute 'drop view if exists ' || quote_ident(view_project) || ' cascade;';
                execute 'create view ' || quote_ident(view_project) || ' as(
                     select distinct ' || quote_ident(ct_project) || '.*,
                         samples.organism,
                         samples.source_name,
                         samples.molecule,
                         libraries.library_id,
                         libraries.library_type
                     from ' || quote_ident(ct_project) || ' inner join samples on samples.sample_id = ' || quote_ident(ct_project) || '.sample_id
                     left join libraries on libraries.sample_id = samples.sample_id)';
                execute 'comment on view ' || quote_ident(view_project) || $$ is 'Table created by create_crosstab_project_designs() triggered by changes on exp_design (and possibly ther tables)'$$;
            end if;
         end loop;
    return null;
    end;
    $body$
language plpgsql;

-- select create_crosstab_project_designs();

/* MEMO: Each table joined in triggered function must have a trigger associated 
otherwise the cross-tabs will be outdated !!*/

drop trigger if exists trigger_create_crosstab_project_designs on exp_design;
create trigger trigger_create_crosstab_project_designs after insert or update or delete 
    on exp_design
    execute procedure main.create_crosstab_project_designs();

drop trigger if exists trigger_projects_create_crosstab_project_designs on projects;
create trigger trigger_projects_create_crosstab_project_designs after insert or update or delete 
    on projects
    execute procedure main.create_crosstab_project_designs();

drop trigger if exists trigger_project_samples_create_crosstab_project_designs on project_samples;
create trigger trigger_project_samples_create_crosstab_project_designs after insert or update or delete 
    on project_samples
    execute procedure main.create_crosstab_project_designs();
    
-- select * from project_exp_design;

/* ---------------------------------------------------------------------------
   Validate sample_id in samples
*/ ---------------------------------------------------------------------------

DROP FUNCTION main.validate_sample_id();
CREATE OR REPLACE FUNCTION main.validate_sample_id()
  RETURNS trigger AS
$BODY$
    DECLARE 
    invalid_sample_id text; -- This variable will store output of queries cheking for invalid sample_id. It must remain NULL for valid IDs;
    
    BEGIN
    
    -- Get possible sample_ids whose initials are not in contacts or are not capitalized.     
    SELECT samples.sample_id INTO invalid_sample_id
    FROM samples LEFT JOIN contacts ON
    upper(contacts.initials) = regexp_replace(samples.sample_id, '\d{1,}.*', '') -- regexp to extract the initials.
    WHERE upper(contacts.initials) IS NULL
    LIMIT 1;

    IF invalid_sample_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_sample_id: sample_id "%" seems to start with initials not found in table contacts.initials or initials not capitalized', invalid_sample_id;
        RETURN NULL;
    END IF;

    -- Check if sample_ids are composed by two or more capitalized letters followed by 3 or more digits possibly followed by _ and only digit and letters.
    SELECT samples.sample_id INTO invalid_sample_id
    FROM samples WHERE sample_id !~ '(^[A-Z]{2,}[0-9]{3,}$)|([A-Z]{2,}[0-9]{3,}_\w+$)';

    IF invalid_sample_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_sample_id: sample_id "%" seems to be badly formatted. Allowed format must match the regexp "(^[A-Z]{2,}[0-9]{3,}$)|([A-Z]{2,}[0-9]{3,}_\w+$)"', invalid_sample_id;
        RETURN NULL;
    END IF;
    RETURN NULL;    
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

/*
*    UNIT TEST:
*    validate_sample_id();
*    set return value to text before running these tests.
* ---------------------------------
*/
begin; -- Must fail: db not capitalized
update samples set sample_id = 'db001_test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Must fail because dbx not in initials
update samples set sample_id = 'DBX001_test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Must fail because '.' not allowed in sample_id
update samples set sample_id = 'DB001._test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Fail: sample_id can't end with _
update samples set sample_id = 'DB001_' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001_ok' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001_123' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;
------------------------ END OF UNIT TEST -------------------------------------

drop trigger if exists trigger_validate_sample_id on samples;
create constraint trigger trigger_validate_sample_id  after insert or update
    on samples deferrable initially deferred for each row
    execute procedure validate_sample_id();

/* ----------------------------------------------------------------------------
* Validate library_id
-------------------------------------------------------------------------------*/

DROP FUNCTION IF EXISTS main.validate_library_id();
CREATE OR REPLACE FUNCTION main.validate_library_id()
  RETURNS trigger AS
$BODY$
    DECLARE 
    invalid_library_id text; -- This variable will store output of queries cheking for invalid library_id. It must remain NULL for valid IDs;
    
    BEGIN

    -- library_id must contain only letters, numbers and  underscore.
    SELECT library_id INTO invalid_library_id FROM libraries WHERE library_id !~ '(^[a-zA-Z]\w+[a-zA-Z0-9]$)';
    
    IF invalid_library_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_library_id: Invalid library_id format: "%" should contain only letters, numbers and underscores.', invalid_library_id;
        RETURN NULL;
    END IF;

    RETURN NULL;
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

/*
* UNIT TEST:
* set return type to text;
* ---------
*/

begin; -- Fail due to dot
update libraries set library_id = 'db001.test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = 'db001$test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = 'db001_test_do_NOT_delete_me_' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = '_db001_test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Ok
update libraries set library_id = 'db001_test_do_NOT_delete_me_2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

------------------------ END OF UNIT TEST -------------------------------------

drop trigger if exists trigger_validate_library_id on libraries;
create constraint trigger trigger_validate_library_id  after insert or update
    on libraries deferrable initially deferred for each row
    execute procedure validate_library_id();
    
/* ----------------------------------------------------------------------------
 Validate fastqfile name by checking it starts with a library_id name
----------------------------------------------------------------------------- */

DROP FUNCTION IF EXISTS main.fastqfile_startswith_library_id();
CREATE OR REPLACE FUNCTION main.fastqfile_startswith_library_id()
  RETURNS trigger AS
$BODY$
    DECLARE 
    invalid_fastqfile text[]; -- This variable will store output of queries cheking for invalid library_id. It must remain NULL for valid IDs;
    
    BEGIN

    SELECT array[fastqfiles.fastqfile, fastqfiles.library_id] INTO invalid_fastqfile FROM fastqfiles
    WHERE get_library_id(fastqfile) != fastqfiles.library_id OR get_library_id(fastqfile) is null;
    
    IF invalid_fastqfile IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger fastqfile_startswith_library_id: Invalid name for fastqfile:
"%" should begin with the name of the assigned library_id "%"', invalid_fastqfile[1], invalid_fastqfile[2];
        RETURN NULL;
    END IF;

    RETURN NULL;
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;
  
----------------------------------[ Unit test ]-------------------------------
-- Set return type to text

begin; -- Fail
update fastqfiles set fastqfile = 'missing.fq.gz' where library_id = 'db001_test_do_NOT_delete_me';
select fastqfile_startswith_library_id();
rollback;

begin; -- Fail
update fastqfiles set fastqfile = 'ds001.fq.gz' where library_id = 'db001_test_do_NOT_delete_me';
select fastqfile_startswith_library_id();
rollback;

begin; -- Ok
update fastqfiles set fastqfile = 'db001_test_do_NOT_delete_me.sanger.fq.gz' where library_id = 'db001_test_do_NOT_delete_me';
select fastqfile_startswith_library_id();
rollback;
-------------------------------------------------------------------------------

drop trigger if exists trigger_fastqfile_startswith_library_id on fastqfiles;
create constraint trigger trigger_fastqfile_startswith_library_id after insert or update
    on fastqfiles deferrable initially deferred for each row
    execute procedure fastqfile_startswith_library_id();